unit CheckPodrazd;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, dRoot, DB, StdCtrls, Buttons, ExtCtrls, ComCtrls,
  cxLookAndFeelPainters, dxSkinsCore, dxSkinBlack, dxSkinBlue,
  dxSkinCaramel, dxSkinCoffee, dxSkinDarkSide, dxSkinGlassOceans,
  dxSkiniMaginary, dxSkinLilian, dxSkinLiquidSky, dxSkinLondonLiquidSky,
  dxSkinMcSkin, dxSkinMoneyTwins, dxSkinOffice2007Black,
  dxSkinOffice2007Blue, dxSkinOffice2007Green, dxSkinOffice2007Pink,
  dxSkinOffice2007Silver, dxSkinPumpkin, dxSkinSilver, dxSkinStardust,
  dxSkinSummer2008, dxSkinsDefaultPainters, dxSkinValentine,
  dxSkinXmas2008Blue, Menus, cxButtons, cxLabel, cxTextEdit, cxMaskEdit,
  cxButtonEdit, cxControls, cxContainer, cxEdit, cxGroupBox, cxRadioGroup,
  cxStyles, dxSkinscxPCPainter, cxCustomData, cxGraphics, cxFilter, cxData,
  cxDataStorage, cxDBData, cxGridCustomTableView, cxGridTableView,
  cxGridDBTableView, IBCustomDataSet, IBQuery, cxGridLevel, cxClasses,
  cxGridCustomView, cxGrid, cxMemo, cxDropDownEdit, cxCalendar;

type
  TfmCheckPodrazd = class(TdlgRoot)
    Panel2: TPanel;
    rgConditions: TcxRadioGroup;
    rgPodr: TcxRadioGroup;
    bePodrazd: TcxButtonEdit;
    cxLabel1: TcxLabel;
    bShow: TcxButton;
    dbgPodr: TcxGrid;
    tvPodr: TcxGridDBTableView;
    cxGridLevel2: TcxGridLevel;
    qResult: TIBQuery;
    qResultPLASTNAME: TIBStringField;
    qResultPNAME: TIBStringField;
    qResultPPATRONYMIC: TIBStringField;
    qResultPDATEBIRTH: TDateField;
    tvPodrPLASTNAME: TcxGridDBColumn;
    tvPodrPNAME: TcxGridDBColumn;
    tvPodrPPATRONYMIC: TcxGridDBColumn;
    tvPodrPDATEBIRTH: TcxGridDBColumn;
    qTmp: TIBQuery;
    cxLabel2: TcxLabel;
    beService: TcxButtonEdit;
    rgServeces: TcxRadioGroup;
    qSd_osn: TIBQuery;
    qsd_other: TIBQuery;
    cxMemo1: TcxMemo;
    qResultSDNAME: TIBStringField;
    tvPodrSDNAME: TcxGridDBColumn;
    qTmp2: TIBQuery;
    qTmp3: TIBQuery;
    qTmp4: TIBQuery;
    qTmp5: TIBQuery;
    deDate: TcxDateEdit;
    cxLabel3: TcxLabel;
    qResultPODR: TIBStringField;
    tvPodrPODR: TcxGridDBColumn;
    procedure bePodrazdPropertiesButtonClick(Sender: TObject;
      AButtonIndex: Integer);
    procedure bShowClick(Sender: TObject);
    procedure cxButtonEdit1PropertiesButtonClick(Sender: TObject;
      AButtonIndex: Integer);
    procedure rgConditionsPropertiesChange(Sender: TObject);
    procedure rgServecesPropertiesChange(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure rgPodrPropertiesChange(Sender: TObject);
    procedure deDatePropertiesChange(Sender: TObject);
  private
    { Private declarations }
    fPodrazd, ufPodrazd: string;
    fServ, ufServ: string;
  public
    { Public declarations }
  end;

var
  fmCheckPodrazd: TfmCheckPodrazd;

implementation
uses DBinv, invtypes, invproc, Filter;
{$R *.dfm}

procedure TfmCheckPodrazd.bePodrazdPropertiesButtonClick(Sender: TObject;
  AButtonIndex: Integer);
begin
  inherited;
  try
    if fmFilter = nil then
      fmFilter := TfmFilter.Create(self);

    fmFilter.qFilter.ParamByName('TFNUM').AsInteger := 95;
    case rgPodr.EditValue of
      0: fmFilter.n_sys_dic := 13;
      1: fmFilter.n_sys_dic := 14;
    end;

    fmFilter.bExtend.Visible := False;
    fmFilter.is_save := False;
    if fmFilter.ShowModal = mrOk then begin
      fPodrazd := '';
      ufPodrazd := '';
      fPodrazd := fmFilter.filter;
      ufPodrazd := fmFilter.ufilter;
      bePodrazd.Text := ufPodrazd;
    end;
  finally
    fmFilter.Release;
    fmFilter := nil;
  end;
  qResult.Close;
end;

procedure TfmCheckPodrazd.bShowClick(Sender: TObject);
begin
  inherited;
  qResult.Close;
  qResult.SQL.Clear;

  case rgConditions.EditValue of
    0: begin

        qResult.SQL.Text := qTmp.SQL.Text;
        if beService.Text <> '' then
          qResult.SQL.Add(' and  ( ' + fServ + ' )');
        qResult.SQL.Add('AND NOT EXISTS (');
        qResult.SQL.Add('SELECT DP.DPID');
        case rgServeces.EditValue of
          0: qResult.SQL.Add('FROM DETI_PODRAZD DP ');
          1: qResult.SQL.Add('FROM DETI_PODRAZD_MISC DP ');
        end;
        qResult.SQL.Add('INNER JOIN SERV_PODR_LINK SPL ON SPL.SDID = DP.SDID');
        qResult.SQL.Add('WHERE DP.PID = P.PID AND SPL.STID = ST.STID');
        qResult.SQL.Add('AND :data BETWEEN cast(DP.DPDATEIN AS DATE) AND CAST(COALESCE(DP.DPDATEOUT, CURRENT_TIMESTAMP) AS DATE))');
//        qResult.SQL.Add('AND cast(DP.DPDATEIN AS DATE) <= COALESCE(SC.SCDATEEND, CURRENT_DATE)');
//        qResult.SQL.Add('AND CAST(COALESCE(DP.DPDATEOUT, CURRENT_TIMESTAMP) AS DATE) >= SC.SCDATEBEGIN )');

      end;
    1: begin

        case rgPodr.EditValue of
          0: qResult.SQL.Text := qTmp2.SQL.Text;
          1: qResult.SQL.Text := qTmp3.SQL.Text;
        end;

        if bePodrazd.Text <> '' then
          qResult.SQL.Add(' and  ( ' + fPodrazd + ' )');

      end;
    2: begin

        case rgPodr.EditValue of
          0: qResult.SQL.Text := qTmp4.SQL.Text;
          1: qResult.SQL.Text := qTmp5.SQL.Text;
        end;

        if bePodrazd.Text <> '' then
          qResult.SQL.Add(' and  ( ' + fPodrazd + ' )');

      end;

  end;
  qResult.ParamByName('data').AsDate := deDate.Date;
  qResult.Open;
  cxMemo1.Clear;
  cxMemo1.Lines.Add(qResult.SQL.Text);
end;



procedure TfmCheckPodrazd.cxButtonEdit1PropertiesButtonClick(
  Sender: TObject; AButtonIndex: Integer);
begin
  inherited;
  try
    if fmFilter = nil then
      fmFilter := TfmFilter.Create(self);

    fmFilter.qFilter.ParamByName('TFNUM').AsInteger := 94;
    fmFilter.qSD.SelectSQL.Clear;

    fmFilter.n_sys_dic := -1;
    case rgServeces.EditValue of
      0: fmFilter.qSD.SelectSQL.Text := qSd_osn.SQL.Text;
      1: fmFilter.qSD.SelectSQL.Text := qsd_other.SQL.Text;
    end;
    fmFilter.bExtend.Visible := False;
    fmFilter.is_save := False;
    if fmFilter.ShowModal = mrOk then begin
      fServ := '';
      ufServ := '';
      fServ := fmFilter.filter;
      ufServ := fmFilter.ufilter;
      beService.Text := ufServ;
    end;
  finally
    fmFilter.Release;
    fmFilter := nil;
  end;
  qResult.Close;
end;

procedure TfmCheckPodrazd.rgConditionsPropertiesChange(Sender: TObject);
begin
  inherited;
  case rgConditions.EditValue of
    0: begin
        beService.Enabled := True;
        rgServeces.Enabled := True;
        bePodrazd.Enabled := False;
        bePodrazd.Text := '';
        fPodrazd := '';
        ufPodrazd := '';
        rgPodr.Enabled := False;
      end;
    1, 2: begin
        beService.Enabled := False;
        rgServeces.Enabled := False;
        beService.Text := '';
        fServ := '';
        ufServ := '';

        bePodrazd.Enabled := True;
        rgPodr.Enabled := True;
      end;

  end;
  qResult.Close;
end;

procedure TfmCheckPodrazd.rgServecesPropertiesChange(Sender: TObject);
begin
  inherited;
  fServ := '';
  ufServ := '';
  beService.Text := '';
  qResult.Close;
end;

procedure TfmCheckPodrazd.FormShow(Sender: TObject);
begin
  inherited;
  deDate.Date := Date;
  rgConditionsPropertiesChange(rgConditions);
end;

procedure TfmCheckPodrazd.rgPodrPropertiesChange(Sender: TObject);
begin
  inherited;
  bePodrazd.Text := '';
  fPodrazd := '';
  ufPodrazd := '';
  qResult.Close;
end;

procedure TfmCheckPodrazd.deDatePropertiesChange(Sender: TObject);
begin
  inherited;
  qResult.Close;
end;

end.

